#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-早间开机-完播率
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 数据分析-早间开机-服务推送成功率 analysis_zjkj_push_rate
def insertStartupRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjkj_complete_rate t where 1=1  and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]

        # 获取当日早间开机 早上启动播报数量
        showNumSql = """
                    select count(DISTINCT(t.active_id)) from log_zjkj_page_show t where 1=1 and t.result =1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                """ % date;
        cur.execute(showNumSql)
        showNumResult = cur.fetchone()
        # 获取所有早上启动播报数量
        showAllNumSql = """
                    select count(1) from log_zjkj_page_show t where 1=1 and t.result =1 
                """ ;
        cur.execute(showAllNumSql)
        showAllNumResult = cur.fetchone()
        # 获取当日早间开机 早上播报音乐完播数
        musicCompleteNumSql = """
                           select count(DISTINCT(t.active_id)) from log_zjkj_page_complete t where t.scene ='music' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d')= '%s'
                       """ % date;
        cur.execute(musicCompleteNumSql)
        musicNumResult = cur.fetchone()
        # 获取当日早间开机 早上播报天气完播数
        weatherCompleteNumSql = """
                                   select count(DISTINCT(t.active_id)) from log_zjkj_page_complete t where t.scene ='weather' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d')= '%s'
                               """ % date;
        cur.execute(weatherCompleteNumSql)
        weatherNumResult = cur.fetchone()
        # 获取当日早间开机 早上播报音乐完播数
        newsCompleteNumSql = """
                                   select count(DISTINCT(t.active_id)) from log_zjkj_page_complete t where t.scene ='news' and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d')= '%s'
                               """ % date;
        cur.execute(newsCompleteNumSql)
        newsNumResult = cur.fetchone()
        # 获取音乐 天气 新闻完播数及 完播率
        musicCompleteRate = 0
        weatherCompleteRate = 0
        newsCompleteRate = 0
        musicCompleteNum = musicNumResult[0]
        weatherCompleteNum = weatherNumResult[0]
        newsCompleteNum = newsNumResult[0]
        showNum = showNumResult[0]
        showAllNum = showAllNumResult[0]

        if showNum != 0:
            if musicCompleteNum > showNum:
                musicCompleteRate = 100
            else:
                musicCompleteRate = round(musicCompleteNum/showNum,4)*100

            if weatherCompleteNum > showNum:
                weatherCompleteRate = 100
            else:
                weatherCompleteRate = round(weatherCompleteNum / showNum, 4) * 100

            if newsCompleteNum >  showNum:
                newsCompleteRate = 100
            else:
                newsCompleteRate = round(newsCompleteNum / showNum, 4) * 100


        if countNum == 0:
            logDetail = "%s日新增-早间开机开启数量为%s,累计开机数量为%s,音乐完播数为%s,完播率为%s,天气完播数为%s,完播率为%s,新闻完播数为%s,完播率为%s" %(
                date,showNum,showAllNum,musicCompleteNum,musicCompleteRate,weatherCompleteNum,weatherCompleteRate,newsCompleteNum,newsCompleteRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjkj_complete_rate(show_num,cumulate_num,music_complete_num,weather_complete_num,news_complete_num
            ,music_complete_rate,weather_complete_rate,news_complete_rate,ref_date) 
                value(%s,%s,%s,%s,%s,%s,%s,%s,'%s')""" % (showNum,showAllNum,musicCompleteNum,weatherCompleteNum,newsCompleteNum,musicCompleteRate,weatherCompleteRate,newsCompleteRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-早间开机开启数量为%s,累计开机数量为%s,音乐完播数为%s,完播率为%s,天气完播数为%s,完播率为%s,新闻完播数为%s,完播率为%s" %(
                date, showNum,showAllNum, musicCompleteNum, musicCompleteRate, weatherCompleteNum, weatherCompleteRate,
                   newsCompleteNum, newsCompleteRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_zjkj_complete_rate set show_num = %s ,cumulate_num = %s,music_complete_num = %s,weather_complete_num = %s ,news_complete_num = %s ,
                music_complete_rate = %s,weather_complete_rate = %s ,news_complete_rate = %s , update_time = now()  where
                    ref_date = '%s' 
            """ % (showNum,showAllNum, musicCompleteNum,weatherCompleteNum,newsCompleteNum,
                            musicCompleteRate,weatherCompleteRate,newsCompleteRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-早间开机-服务推送成功率 analysis_zjkj_push_rate --本月
def insertStartupRateMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当月 数据
        selectCountSql = """
                    select count(1) num from analysis_zjkj_complete_rate t where 1=1  and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当月 早间开机 早上启动播报数量
        showNumSql = """
                           select sum(show_num) from analysis_zjkj_complete_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(showNumSql)
        showNumResult = cur.fetchone()
        # 获取当月早间开机 早上播报音乐完播数
        musicCompleteNumSql = """
                           select sum(music_complete_num) from analysis_zjkj_complete_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(musicCompleteNumSql)
        musicNumResult = cur.fetchone()
        # 获取所有早上启动播报数量
        showAllNumSql = """
                    select count(1) from log_zjkj_page_show t where 1=1 and t.result =1 
                """;
        cur.execute(showAllNumSql)
        showAllNumResult = cur.fetchone()
        # 获取当月早间开机 早上播报天气完播数
        weatherCompleteNumSql = """
                           select sum(weather_complete_num) from analysis_zjkj_complete_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(weatherCompleteNumSql)
        weatherNumResult = cur.fetchone()
        # 获取当月早间开机 早上播报音乐完播数
        newsCompleteNumSql = """
                           select sum(news_complete_num) from analysis_zjkj_complete_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(newsCompleteNumSql)
        newsNumResult = cur.fetchone()
        # 获取音乐 天气 新闻完播数及 完播率
        musicCompleteRate = 0
        weatherCompleteRate = 0
        newsCompleteRate = 0
        musicCompleteNum = musicNumResult[0]
        weatherCompleteNum = weatherNumResult[0]
        newsCompleteNum = newsNumResult[0]
        showNum = showNumResult[0]
        showAllNum = showAllNumResult[0]

        if showNum != 0:
            if musicCompleteNum > showNum:
                musicCompleteRate = 100
            else:
                musicCompleteRate = round(musicCompleteNum/showNum,4)*100

            if weatherCompleteNum > showNum:
                weatherCompleteRate = 100
            else:
                weatherCompleteRate = round(weatherCompleteNum / showNum, 4) * 100

            if newsCompleteNum >  showNum:
                newsCompleteRate = 100
            else:
                newsCompleteRate = round(newsCompleteNum / showNum, 4) * 100


        if countNum == 0:
            logDetail ="%s日新增-早间开机开启数量为%s,累计开机数量为%s,音乐完播数为%s,完播率为%s,天气完播数为%s,完播率为%s,新闻完播数为%s,完播率为%s" %(
                toMonthDay,showNum,showAllNum,musicCompleteNum,musicCompleteRate,weatherCompleteNum,weatherCompleteRate,newsCompleteNum,newsCompleteRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjkj_complete_rate(show_num,cumulate_num,music_complete_num,weather_complete_num,news_complete_num
            ,music_complete_rate,weather_complete_rate,news_complete_rate,ref_date) 
                value(%s,%s,%s,%s,%s,%s,%s,%s,'%s')""" % (showNum,showAllNum,musicCompleteNum,weatherCompleteNum,newsCompleteNum,musicCompleteRate,weatherCompleteRate,newsCompleteRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail ="%s日更新-早间开机开启数量为%s,累计开机数量为%s,音乐完播数为%s,完播率为%s,天气完播数为%s,完播率为%s,新闻完播数为%s,完播率为%s" %(
                toMonthDay, showNum, showAllNum,musicCompleteNum, musicCompleteRate, weatherCompleteNum, weatherCompleteRate,
                   newsCompleteNum, newsCompleteRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_zjkj_complete_rate set show_num = %s,cumulate_num = %s ,music_complete_num = %s,weather_complete_num = %s ,news_complete_num = %s ,
                music_complete_rate = %s,weather_complete_rate = %s ,news_complete_rate = %s , update_time = now()  where
                    ref_date = '%s' 
            """ % (showNum, showAllNum,musicCompleteNum,weatherCompleteNum,newsCompleteNum,
                            musicCompleteRate,weatherCompleteRate,newsCompleteRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 30
        jobName = '核心指标-早间开机-完播率'
        tableName = 'analysis_zjkj_complete_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print ("%s 日期,执行<数据分析-早间开机-完播率>报表" % date)
    insertStartupRate(date)
    insertStartupRateMonth(date,monthdate)

